package com.bst.etl.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.bst.common.enums.DataSourceTypeEnum;
import com.bst.etl.factory.dbtype.DbTypeMappingFactory;
import com.bst.etl.factory.dbtype.ITypeMappingStrategy;
import com.bst.framework.datasource.DynamicDataSource;
import com.bst.md.vo.TableFieldDiffVO;
import com.bst.md.vo.TableFieldVO;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.ddlutils.DatabaseOperationException;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;


public class DDLUtil {

    /**
     * 表名转查询
     * @return
     */
    public static String tableChangeToQuery(String tableName) {
       return "select * from "+tableName+" tt where 1=2";
    }

    public static Map<String, List<TableFieldVO>> compareFields(List<TableFieldVO> source, List<TableFieldVO> target) {
        Map<String, List<TableFieldVO>> result = new HashMap<>();

        // 相同name的字段
        List<TableFieldVO> sameName = source.stream()
                .filter(s -> target.stream().anyMatch(t -> t.getName().equals(s.getName())))
                .collect(Collectors.toList());

        // 只在source中的字段
        List<TableFieldVO> onlyInSource = source.stream()
                .filter(s -> target.stream().noneMatch(t -> t.getName().equals(s.getName())))
                .collect(Collectors.toList());

        // 只在target中的字段
        List<TableFieldVO> onlyInTarget = target.stream()
                .filter(t -> source.stream().noneMatch(s -> s.getName().equals(t.getName())))
                .collect(Collectors.toList());
        result.put("same", sameName);
        result.put("onlyInSource", onlyInSource);
        result.put("onlyInTarget", onlyInTarget);
        return result;
    }

    public static void changeToDiffVO(List<TableFieldDiffVO> diffVOS,List<TableFieldVO> vos,String type,String typeName) {
        TableFieldDiffVO diffVO;
        for (TableFieldVO vo : vos) {
            diffVO = new TableFieldDiffVO();
            try {
                BeanUtils.copyProperties(diffVO,vo); // 复制同名属性
                diffVO.setDiffType(type); // 设置扩展属性
                diffVO.setDiffTypeDes(typeName); // 设置扩展属性
                diffVOS.add(diffVO);
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            } catch (InvocationTargetException e) {
                throw new RuntimeException(e);
            }

        }
    }

    public static void migrateSchema(DataSource sourceDataSource,DataSource targetDataSource) throws DatabaseOperationException, SQLException {
        // 1. 从源数据库读取模型
        Database sourceDb = readDatabaseModel(sourceDataSource);

        // 2. 生成目标数据库的建表SQL
        String createSql = generateCreateTablesSql(sourceDb,targetDataSource);

        // 3. 在目标数据库执行SQL
        //executeSqlOnTarget(createSql);

        // 4. 可选：导出SQL到文件
        //exportSqlToFile(createSql, "schema-migration.sql");
    }

    public static List<TableFieldVO> getColumnsFromSqlResult(String sql, DruidDataSource dataSource) throws SQLException {
        if(dataSource==null) {
            dataSource = DynamicDataSource.getDataSourceById("dw");
        }
        List<TableFieldVO> columns = new ArrayList<>();
        DataSourceTypeEnum typeEnum = DataSourceTypeEnum.valueOf(dataSource.getDbType().toUpperCase());
        ITypeMappingStrategy strategy = DbTypeMappingFactory.getStrategy(typeEnum);
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                TableFieldVO columnInfo = new TableFieldVO();
                columnInfo.setName(metaData.getColumnName(i).toUpperCase());
                columnInfo.setType(metaData.getColumnType(i));
                columnInfo.setTypeName(metaData.getColumnTypeName(i));
                columnInfo.setPrecision(metaData.getPrecision(i));
                columnInfo.setScale(metaData.getScale(i));
                columnInfo.setDbTypeName(strategy.mapType(columnInfo.getType(),columnInfo.getPrecision(), columnInfo.getScale()));
                columns.add(columnInfo);
            }
        }
        return columns;
    }

    public static String getCreateTableSql(String sql, String tableName,DruidDataSource source, DruidDataSource target) throws Exception {
        if(source==null) {
            source = DynamicDataSource.getDataSourceById("dw");
        }
        if(target==null) {
            target = DynamicDataSource.getDataSourceById("dw");
        }
        List<TableFieldVO> columns = new ArrayList<>();
        ITypeMappingStrategy strategy = DbTypeMappingFactory.getStrategy(DataSourceTypeEnum.valueOf(target.getDbType().toUpperCase()));
        StringBuffer creatSql = new StringBuffer("create table "+tableName+" ("+strategy.primaryKey());
        try (Connection conn = source.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                creatSql.append(metaData.getColumnName(i).toUpperCase()+" "+strategy.mapType(metaData.getColumnType(i),metaData.getPrecision(i),metaData.getScale(i)));
                if(i==columnCount) {
                    creatSql.append(")");
                } else {
                    creatSql.append(",");
                }
            }
        }
        return creatSql.toString();
    }

    public static void exec(String sql) throws Exception {
        List<TableFieldVO> columns = new ArrayList<>();
        try (Connection conn = DynamicDataSource.getDataSourceById("dw").getConnection();
             Statement stmt = conn.createStatement();) {
            stmt.execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException("建表失败,"+e.getMessage());
        }
    }

    private static Database readDatabaseModel(DataSource sourceDataSource) throws DatabaseOperationException {
        Platform platform = PlatformFactory.createNewPlatformInstance(sourceDataSource);
        return platform.readModelFromDatabase("sourceModel");
    }

    private static  String generateCreateTablesSql(Database sourceDb,DataSource targetDataSource) throws SQLException {
        Platform targetPlatform = PlatformFactory.createNewPlatformInstance(targetDataSource);
        return targetPlatform.getCreateTablesSql(sourceDb, false, false);
    }

//    private void executeSqlOnTarget(String[] sqls) throws SQLException {
//        try (Connection conn = targetDataSource.getConnection();
//             Statement stmt = conn.createStatement()) {
//            for (String sql : sqls) {
//                try {
//                    stmt.execute(sql);
//                    System.out.println("Executed: " + sql);
//                } catch (SQLException e) {
//                    System.err.println("Failed to execute: " + sql);
//                    System.err.println("Error: " + e.getMessage());
//                }
//            }
//        }
//    }

//    private void exportSqlToFile(String[] sqls, String filename) throws IOException {
//        Path path = Paths.get(filename);
//        Files.write(path, Arrays.asList(sqls), StandardCharsets.UTF_8);
//        System.out.println("SQL exported to: " + path.toAbsolutePath());
//    }
}
